package com.chen.bankbeijing;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;

public class DBTest {
	public java.sql.Connection connection;
	private java.sql.PreparedStatement preparedStatement;

	public DBTest() {
		try {
			connection = DriverManager.getConnection(
					"jdbc:mysql://192.168.0.248/test?useUnicode=true&characterEncoding=UTF-8", "root",
					"a+rquQe5KMGKKkmFJkl0");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void close() {
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public ResultSet executeQuery(String sql, LinkedList<Object> params) {
		try {
			preparedStatement = connection.prepareStatement(sql);
			if (params != null) {
				int i = 1;
				for (Object p : params) {
					preparedStatement.setObject(i++, p);
				}
			}
			return preparedStatement.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public int executeUpdate(String sql, LinkedList<Object> params) {
		try {
			preparedStatement = connection.prepareStatement(sql);
			if (params != null) {
				int i = 1;
				for (Object p : params) {
					preparedStatement.setObject(i++, p);
				}
			}
			return preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;
	}

	public static void main(String[] args) throws Exception {
		DBTest tool = new DBTest();  
		Connection connection = tool.connection;
		try {
			connection.setAutoCommit(false);
			//String sql = "update order_info set state=5,aporderid=12345 where canceled=1 and state= 6 and id=289";
			String sql = "update test_bal set balance = balance - 1 where  balance >=1 ";
			int j = tool.executeUpdate(sql,null);
			System.out.println("j=" + j);
			// 遍历结束，提交事物
			connection.commit();
		} catch (Exception e) {
			try {
				connection.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			if (connection != null) {
				connection.close();
			}

		}
	}
}
